package com.mentaleye.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;

import com.mentaleye.dal.model.StudentPunchRecord;
import com.mentaleye.dal.model.TimeRecord;

public class ExcelInputUtil {
	@SuppressWarnings("deprecation")
	private static int getStudentId(HSSFRow row, short cellLength) {
		HSSFCell cell = null;
		Object cellValue = null;
		StringBuilder sb = new StringBuilder();
		for (int j = 0; j <= cellLength; j++) {
			cell = row.getCell(j);
			if (cell != null) {
				switch (cell.getCellTypeEnum()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					if (cellValue != null) {
						sb.append(cellValue);
					}
					break;

				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						cellValue = cell.getDateCellValue();
					} else {
						cellValue = cell.getNumericCellValue();
					}

					if (cellValue != null) {
						sb.append(cellValue);
					}
					break;

				default:
					break;
				}
			}
		}
		
		String headString= sb.toString();
		int idBeginIndex = headString.indexOf(":", 0) + 1;
		int idEndIndex = headString.indexOf("姓", idBeginIndex);		
		return Integer.valueOf(headString.substring(idBeginIndex, idEndIndex));
	}
	
	@SuppressWarnings("deprecation")
	private static String getYearAndMonth(HSSFRow row) {
		StringBuilder sb = new StringBuilder();
		HSSFCell cell = null;
		int cellLength = row.getLastCellNum();
		for (int j = 0; j <= cellLength; j++) {
			Object cellValue = "";
			cell = row.getCell(j);
			if (cell != null) {
				switch (cell.getCellTypeEnum()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					break;

				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						cellValue = cell.getDateCellValue();
					} else {
						cellValue = cell.getNumericCellValue();
					}
					break;

				default:
					break;
				}
				sb.append(cellValue.toString());
			}
		}
		String value = sb.toString();
		int beginIndex = value.indexOf("2");
		return value.substring(beginIndex, beginIndex + 7);
	}
	
	/*
	private static String getDate(String yearAndMonth,String dateNum,String timeRecord) {
		return yearAndMonth+"-"+dateNum+" "+timeRecord;
	}
	*/
	
	@SuppressWarnings("deprecation")
	private static List<String> getDateList(HSSFRow row,String yearAndMonth) {
		HSSFCell cell = null;
		List<String> dateList = new ArrayList<String>();
		int cellLength = row.getLastCellNum();
		for (int j = 0; j <cellLength; j++) {
			Object cellValue = "";
			cell = row.getCell(j);
			if (cell != null) {
				switch (cell.getCellTypeEnum()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					break;

				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						cellValue = cell.getDateCellValue();
					} else {
						cellValue = cell.getNumericCellValue();
					}
					break;

				default:
					break;
				}

				if (cellValue!=null&&!cellValue.toString().equals("")) {
					String value = cellValue.toString();
					int endIndex = value.indexOf(".");
					if (endIndex == 2) {
						value = value.substring(0, endIndex);
					} else {
						value = "0" + value.substring(0, endIndex);
					}
					dateList.add(yearAndMonth+"-"+value);
				}
			}
		}
		return dateList;
	}

	private static TimeRecord getTimeRecord(String recordValue) {
		TimeRecord timeRecord = null;
		int tailLength = 0;
		String beginTime = null;
		String endTime = null;
		if (recordValue == null) {
			timeRecord = new TimeRecord();
		} else {
			tailLength = recordValue.length();
			if (tailLength > 0) {
				beginTime = recordValue.substring(0, 5);
				timeRecord = new TimeRecord();
				timeRecord.setBeginTime(beginTime);
				if (tailLength > 6) {
					endTime = recordValue.substring(tailLength - 5);
					timeRecord.setEndTime(endTime);
				}
			}
		}
		return timeRecord;
	}
	
	@SuppressWarnings("deprecation")
	private static List<TimeRecord> getTimeRecordList(HSSFRow row, short cellLength) {
		List<TimeRecord> timeRecordList = new ArrayList<TimeRecord>();
		HSSFCell cell = null;
		TimeRecord timeRecord = null;
		for (int j = 0; j <= cellLength; j++) {
			Object cellValue = null;
			cell = row.getCell(j);
			if (cell != null) {
				switch (cell.getCellTypeEnum()) {
				case STRING:
					cellValue = cell.getStringCellValue();
					break;

				case NUMERIC:
					if (DateUtil.isCellDateFormatted(cell)) {
						cellValue = cell.getDateCellValue();
					} else {
						cellValue = cell.getNumericCellValue();
					}
					break;

				default:
					break;
				}
				if (cellValue != null) {
					timeRecord = getTimeRecord(cellValue.toString());
				} 
			} 

			timeRecordList.add(timeRecord);
		}
		return timeRecordList;
	}

	public static List<StudentPunchRecord> getExcelData(String path) throws IOException {
		List<StudentPunchRecord> recordList = new ArrayList<StudentPunchRecord>();

		int rowLength = 0;
		short cellLength = 0;
		File file = new File(path);
		FileInputStream fis = null;
		HSSFWorkbook workBook = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;

		StudentPunchRecord record = null;
		List<String> dateList=null;
		List<TimeRecord> timeList =null;
		String dateNum=null;
		String yearAndMonth=null;
		int num=0;
		int studentId=0;
		if (file.exists() && file.canRead()) {
			fis = new FileInputStream(file);
			workBook = new HSSFWorkbook(fis);
			sheet = workBook.getSheet("考勤记录");
			rowLength = sheet.getLastRowNum();
			yearAndMonth=getYearAndMonth(sheet.getRow(2));
			dateList=getDateList(sheet.getRow(3),yearAndMonth);
			
			for (int i = 4; i <rowLength; i++) {
				row = sheet.getRow(i);
				cellLength = row.getLastCellNum();
				if (i % 2 == 0) {
					studentId=getStudentId(row, cellLength);
				} else {
					timeList = getTimeRecordList(row, cellLength);
					for(int j=0;j<dateList.size();j++){
						record=new StudentPunchRecord();
						record.setStudentId(studentId);
						
						num=j+1;
						if (num<10) {
							dateNum="0"+num;
						} 
						else
						{
							dateNum=String.valueOf(num);
						}
						record.setWriteTime(DateFormatter.parse(yearAndMonth+"-"+dateNum, "yyyy-MM-dd"));
						
						if(timeList.get(j)!=null&&timeList.get(j).getBeginTime()!=null){
							record.setFirstTime(timeList.get(j).getBeginTime());
						}
						if(timeList.get(j)!=null&&timeList.get(j).getEndTime()!=null){
							record.setLastTime(timeList.get(j).getEndTime());
						}
						
						recordList.add(record);
					}
				}
			}
		}
		return recordList;
	}
}
